﻿using System;
using System.Diagnostics;
using System.IO;
using BLToolkit.Data;
using dbupd.context;
using dbvc.Context;
using dbvc.context.Project;
using dbvc.context.Script;
using dbvc.context.Version;
using dbvc.context.Security;
using dbvc.data;
using NUnit.Framework;

namespace tdd.dbupd
{
    [TestFixture]
    public class update
    {
        [SetUp]
        [TearDown]
        public void tear_down()
        {
            using(var db = new DbManager())
            {
                db.SetSpCommand("CLEAR").ExecuteNonQuery();
            }
        }

        [Test]
        public void update_first_run()
        {
           // create_test_package();
            create_test_vova_package(); //Vova's tests

            new Update("tmp_db", @"C:\Temp\result.xml", s => SetStatus(s)).Execute();
        }

        public void SetStatus(string text)
        {
            Debug.WriteLine("-> " + text);
        }

        private void create_test_vova_package()
        {
            var security = new Security();
            
            security.Login("root","root");

            var projTest = new CreateProject(security.Principal, new db_project() { name = "TEST", active = true }).Execute();

            var v1 = new CreateVersion(security.Principal, projTest, new db_version() { name = "0.1", on_date = DateTime.Today.AddDays(1) }).Execute();
            
            //CREATE TABLE SCRIPT
            var s1 = new CreateScript(security.Principal, new db_script() { name = "create_table_script_1", active = true, text = "CREATE TABLE test_tbl_1(ID INT NOT NULL IDENTITY,[NAME] NVARCHAR(100) NOT NULL UNIQUE,CONSTRAINT PK PRIMARY KEY (ID))" }).Execute();

            var s10 = new CreateScript(security.Principal, new db_script() { name = "alter_table_script_1", active = true, text = "ALTER TABLE test_tbl_1 ADD column_a nvarchar(100) " }).Execute();
            var s11 = new CreateScript(security.Principal, new db_script() { name = "alter_table_script_1", active = true, text = "ALTER TABLE test_tbl_1 ADD column_b nvarchar(100) " }).Execute();
            var s12 = new CreateScript(security.Principal, new db_script() { name = "insert_table_script_1", active = true, text = "INSERT INTO test_tbl_1([name], column_a, column_b) VALUES('"+Guid.NewGuid()+"','COL_A','COL_B')" }).Execute();
            var s13 = new CreateScript(security.Principal, new db_script() { name = "insert_table_script_1", active = true, text = "INSERT INTO test_tbl_1([name], column_a, column_b) VALUES('" + Guid.NewGuid() + "','COL_A','COL_B')" }).Execute();
            var s14 = new CreateScript(security.Principal, new db_script() { name = "insert_table_script_1", active = true, text = "INSERT INTO test_tbl_1([name], column_a, column_b) VALUES('" + Guid.NewGuid() + "','COL_A','COL_B')" }).Execute();
            var s15 = new CreateScript(security.Principal, new db_script() { name = "insert_table_script_1", active = true, text = "INSERT INTO test_tbl_1([name], column_a, column_b) VALUES('" + Guid.NewGuid() + "','COL_A','COL_B')" }).Execute();

            var s16 = new CreateScript(security.Principal, new db_script() { name = "update_table_script_1", active = true, text = "UPDATE test_tbl_1 SET column_b='UPDATED_COL_B' WHERE column_b='COL_B'" }).Execute();

            var s17 = new CreateScript(security.Principal, new db_script() { name = "delete_table_script_1", active = true, text = "DELETE FROM test_tbl_1 WHERE column_b='COL_B'" }).Execute();
            //CREATE SP SCRIPT
            var s2 = new CreateScript(security.Principal, new db_script() { name = "create_sp_script_1", active = true, text = "CREATE PROCEDURE test_sp_1(@name NVARCHAR(100)) AS INSERT INTO test_tbl_1([name]) VALUES(@name  + CONVERT(NVARCHAR(50), CURRENT_TIMESTAMP))" }).Execute();

            //EXECUTE PREVIOUSLY CREATED SP
            //@name parameter can violate unique constraint declared in "test_tbl_1" table
            var s3 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '"+ Guid.NewGuid() +"' " }).Execute();
            var s4 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '" + Guid.NewGuid() + "' " }).Execute();
            var s5 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '" + Guid.NewGuid() + "' " }).Execute();

            var s6 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '" + Guid.NewGuid() + "' " }).Execute();
            var s7 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '" + Guid.NewGuid() + "' " }).Execute();
            var s8 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 '" + Guid.NewGuid() + "' " }).Execute();

            var s18 = new CreateScript(security.Principal, new db_script() { name = "alter_procedure_script_1", active = true, text = "ALTER PROC test_sp_1(@name NVARCHAR(100)) AS INSERT INTO test_tbl_1([name]) VALUES(@name  + CONVERT(NVARCHAR(50), @@IDENTITY))" }).Execute();

            var s19 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 'NAME->' " }).Execute();
            var s20 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 'NAME->' " }).Execute();
            var s21 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 'NAME->' " }).Execute();
            var s22 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = "EXEC test_sp_1 'NAME->' " }).Execute();
            
            

            var script1 =@"if not exists (select * from dbo.sysobjects where id = object_id(N'cs_aicclog') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN

	create table cs_aicclog
	(
	primkey int NOT NULL IDENTITY (1, 1),
	datestamp datetime null,
	command varchar(20) null,
	sessionid varchar(20) null,
	emp_id nchar(10) null,
	emp_name nvarchar(50) null,
	emp_site char(2) null,
	code nchar(11) null,
	crse_title nvarchar(60) null,
	aiccdata nvarchar(4000) null,
	aless_key int null,
	action_ varchar(40) null,
	error_no int null,
	err_text nvarchar(2000) null,
	userstamp nchar(10) null,
	) ON [PRIMARY]

	ALTER TABLE cs_aicclog WITH NOCHECK ADD
		CONSTRAINT [PK_cs_aicclog] PRIMARY KEY  CLUSTERED
		(
			[primkey]
		)  ON [PRIMARY]

	CREATE NONCLUSTERED INDEX IX_cs_aicclog ON cs_aicclog
		(
		emp_id,
		code
		) WITH FILLFACTOR = 90 ON [PRIMARY]

	CREATE NONCLUSTERED INDEX IX_cs_aicclog2 ON cs_aicclog
		(
			datestamp,
			userstamp
		) WITH FILLFACTOR = 90 ON [PRIMARY]
END
GO";
            
            var s9 = new CreateScript(security.Principal, new db_script() { name = "execute_sp_script_1", active = true, text = script1 }).Execute();






            new AttachScript(security.Principal, v1, new[] { s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20,s21,s22 }).Execute();

            if (File.Exists(@"C:\Temp\result.xml")) File.Delete(@"C:\Temp\result.xml");

            new CreatePackage(projTest.id, security.Principal, new[] { v1.id }, @"C:\Temp\result.xml").Execute();
            
        }

        private void create_test_package()
        {
            var security = new Security();

            security.Login("root", "root");

            var projTest = new CreateProject(security.Principal, new db_project() { name = "TEST", active = true }).Execute();

            var v1 = new CreateVersion(security.Principal, projTest, new db_version() { name = "0.1", on_date = DateTime.Today.AddDays(1) }).Execute();
            var v2 = new CreateVersion(security.Principal, projTest, new db_version() { name = "0.4", on_date = DateTime.Today.AddDays(4) }).Execute();
            var v4 = new CreateVersion(security.Principal, projTest, new db_version() { name = "0.3", on_date = DateTime.Today.AddDays(3) }).Execute();
            var v3 = new CreateVersion(security.Principal, projTest, new db_version() { name = "0.2", on_date = DateTime.Today.AddDays(2) }).Execute();

            var s1 = new CreateScript(security.Principal, new db_script() { name = "X1", active = true, text = "CREATE TABLE [dbo].[test1](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s2 = new CreateScript(security.Principal, new db_script() { name = "X2", active = true, text = "CREATE TABLE [dbo].[test2](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s3 = new CreateScript(security.Principal, new db_script() { name = "X3", active = true, text = "CREATE TABLE [dbo].[test3](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s4 = new CreateScript(security.Principal, new db_script() { name = "X4", active = true, text = "CREATE TABLE [dbo].[test4](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s5 = new CreateScript(security.Principal, new db_script() { name = "X5", active = true, text = "CREATE TABLE [dbo].[test5](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s6 = new CreateScript(security.Principal, new db_script() { name = "X6", active = true, text = "CREATE TABLE [dbo].[test6](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s7 = new CreateScript(security.Principal, new db_script() { name = "X7", active = true, text = "CREATE TABLE [dbo].[test7](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s8 = new CreateScript(security.Principal, new db_script() { name = "X8", active = true, text = "CREATE TABLE [dbo].[test8](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s9 = new CreateScript(security.Principal, new db_script() { name = "X9", active = true, text = "CREATE TABLE [dbo].[test9](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s10 = new CreateScript(security.Principal, new db_script() { name = "X10", active = true, text = "CREATE TABLE [dbo].[test10](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            var s11 = new CreateScript(security.Principal, new db_script() { name = "X11", active = true, text = "CREATE TABLE [dbo].[test11](	[test] [nchar](10) NULL) ON [PRIMARY]" }).Execute();
            //var s12 = new CreateScript(security.Principal, new db_script() { name = "X12", active = true, text = "" }).Execute();

            new AttachScript(security.Principal, v1, new[] { /*s12,*/ s1, s7 }).Execute();
            new AttachScript(security.Principal, v2, new[] { s2, s4, s3  }).Execute();
            new AttachScript(security.Principal, v3, new[] { s11, s5, s6 }).Execute();
            new AttachScript(security.Principal, v4, new[] { s8, s9, s10 }).Execute();

            if (File.Exists(@"C:\Temp\result.xml")) File.Delete(@"C:\Temp\result.xml");

            new CreatePackage(projTest.id, security.Principal, new[] { v2.id, v4.id, v1.id, v3.id }, @"C:\Temp\result.xml").Execute();
        }
    }
}
